CREATE OR REPLACE PROCEDURE InsertItemDataByPhid (
    master_phid IN NUMBER  -- 将参数定义为 phid，类型为 NUMBER
) as
    v_item_phid NUMBER;
    v_ps_resbase_phid NUMBER;
BEGIN
    -- 遍历满足条件的明细记录
    FOR detail_rec IN (
        SELECT u_pur_name -- 物料名称
        ,pphid
             , u_pur_item_category -- 物料分类
             , u_pur_ggxh -- 规格信号
             , u_unit -- 计量单位
        FROM p_form_equipment_list_d
        WHERE pphid = master_phid   -- 使用 pphid 字段与传入的 phid 参数进行匹配
    ) LOOP
    
        -- 获取今天的记录数，用于生成 v_item_phid
        SELECT COUNT(*)
        INTO v_item_phid
        FROM itemdata
        WHERE TO_CHAR(ng_insert_dt, 'YYYYMMDD') = TO_CHAR(SYSDATE, 'YYYYMMDD');

                SELECT COUNT(*)
        INTO v_ps_resbase_phid
        FROM ps_resbase
        WHERE TO_CHAR(ng_insert_dt, 'YYYYMMDD') = TO_CHAR(SYSDATE, 'YYYYMMDD');

        -- 将生成的唯一 phid 和 item_no 插入到 itemdata 表中
        INSERT INTO itemdata (
            phid
            , itemno
            , itemname
            , phid_resbs
            , spec
            , phid_unit
            ,resource_type
            , positive_dev--正差
            , negative_dev--负差
            , checktype
            , phid_item_property_group
            , cur_orgid
            , ffid
            , ng_phid_cu
            , ng_phid_bp
            , ng_phid_org
            , NG_PHID_UI_SCHEME
            , NG_PHID_ORIGINAL
            , NG_ORGID_ORIGINAL
            , NG_SV_SEARCH_KEY
            , NG_SD_SEARCH_KEY
            , NG_SHARE_SIGN
            ,ng_insert_dt,ng_update_dt
            ,editor,creator
            -- ,weight,shap_l,shap_w,shap_h,area,gravity
            -- ,LOTADD,FIXLOT,ORD_MULT,ORDERCYCLE,ROP,QTY_SECURE,SFTY_TIME,REQLEVEL
            -- ,mfg_lead,pur_lead,insp_lead,cum_lead,leadrate,planlevel,minlot,minlot,ll_code
            ,planprc,countcycle
            ,res_attribute--物料属性
        ) 
        VALUES (
            TO_NUMBER('8880' || TO_CHAR(SYSDATE, 'YYYYMMDD') || LPAD(v_item_phid, 4, '0'))
            , TO_CHAR(SYSDATE, 'YYYYMMDD') || '-' || LPAD(item_no_seq.NEXTVAL, 4, '0')
            , detail_rec.u_pur_name
            , detail_rec.u_pur_item_category
            , detail_rec.u_pur_ggxh
            , detail_rec.u_unit  -- 使用 u_msunit 替代 u_unit
            ,2
            , 0, 0, 3, 0, 1, 7
            , 1
            , 0
            , 1
            , 0
            , 0
            , 0
            , '10'
            , '1'
            , 5
            ,SYSDATE,SYSDATE
            ,8570000000000004,8570000000000004
            -- ,0,0,0,0,0,0
            -- ,0,0,0,0,0,0,0,0
            -- ,0,0,0,0,0,0,0,0,0
            ,0,0
            ,1--物料属性
        );
        -- -- 将生成的 phid_itemdata 和新的 phid 插入到 ps_resbase 表中
        INSERT INTO ps_resbase (
            phid
            ,phid_itemdata
            , ng_phid_cu
            , ng_phid_bp
            , ng_phid_org
            , NG_PHID_UI_SCHEME
            , NG_PHID_ORIGINAL
            , NG_ORGID_ORIGINAL
            , NG_SV_SEARCH_KEY
            , NG_SD_SEARCH_KEY
            , NG_SHARE_SIGN
            ,ng_insert_dt,ng_update_dt
            ,ispub--公共
            ,isequipment
            ,isfarmproduce
            ,ismodel
            ,isstop
        ) 
        VALUES (
            TO_NUMBER('8880' || TO_CHAR(SYSDATE, 'YYYYMM') || LPAD(v_ps_resbase_phid, 4, '0'))
            ,TO_NUMBER('8880' || TO_CHAR(SYSDATE, 'YYYYMMDD') || LPAD(v_item_phid, 4, '0'))
            , 1
            , 0
            , 1
            , 0
            , 0
            , 0
            , '10'
            , '1'
            , 5
            ,SYSDATE,SYSDATE
            ,1
            ,0
            ,0
            ,0
            ,0
            );


    END LOOP;

    -- 提交插入操作（建议在存储过程外部控制事务提交）
    COMMIT;
END InsertItemDataByPhid;

-- 在使用该存储过程之前，请确保已经创建了序列，可以用以下SQL语句创建：
-- CREATE SEQUENCE itemdata_seq START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
-- CREATE SEQUENCE item_no_seq START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
-- CREATE SEQUENCE ps_resbase_seq START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;


-- select * from ps_resbase
-- DELETE FROM ps_resbase WHERE ps_resbase.phid = '8880282411000183';
